import logging
from typing import Optional, List

from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from datebase import get_db

data_app_scene = APIRouter(prefix="/APPScene", tags=["APPScene"])


class AppSearch(BaseModel):
    fsiteNo: str = Field(None, description="客户编号")
    fpointNo: str = Field(None, description="站点编号")
    userNo: str = Field(None, description="用户编号")


@data_app_scene.get("/getAppSceneListByfsiteNo", summary="手工场景 s_tc_scene_head 查询接口")
def get_app_scene_list_by_fsiteno(app: AppSearch = Query(), db: Session = Depends(get_db)):
    param = {"fsiteNo": app.fsiteNo, "fpointNo": app.fpointNo}
    sql = "select a.fsceneno,a.fscenename,a.fsite_no,a.fairitem,a.fscenetype"
    sql = sql + ",a.fteacherno,fwhere,fvalue,fwhere2,fvalue2,fexectime"
    sql = sql + ",fexecweek1,fexecweek2,fexecweek3,fexecweek4,fexecweek5,fexecweek6,fexecweek7"
    sql = sql + ",a.foperator,a.fupddate,a.frange"
    sql = sql + ",b.fpoint_no"
    sql = sql + " from s_tc_scene_head a,s_tc_scene_point b"
    sql = sql + " where a.fscenetype=4"
    sql = sql + " and a.fsceneno=b.fsceneno"
    if app.fsiteNo is not None:
        sql = sql + " and a.fsite_no= :fsiteNo "
    if app.fpointNo is not None:
        sql = sql + " and fpoint_no= :fpointNo "
    data = db.execute(text(sql), param).fetchall()

    # sTcSceneItemList 明细列表
    sql_4 = " select c.fsceneno,c.fno,c.fairitem,d.fariname"
    sql_4 = sql_4 + ",d.ffield,c.fwhere,c.fvalue,c.fwhere2,c.fvalue2 "
    sql_4 = sql_4 + " from s_tc_scene_head a"
    sql_4 = sql_4 + " inner join s_tc_scene_point b on a.fsceneno=b.fsceneno"
    sql_4 = sql_4 + " inner join s_tc_scene_item c on a.fsceneno=c.fsceneno"
    sql_4 = sql_4 + " inner join s_ts_airitem d on d.fairitem=c.fairitem"
    sql_4 = sql_4 + " where a.fscenetype=4 "
    if app.fsiteNo is not None:
        sql_4 = sql_4 + " and a.fsite_no= :fsiteNo "
    if app.fpointNo is not None:
        sql_4 = sql_4 + " and fpoint_no= :fpointNo "
    sql_4 = sql_4 + " order by b.fsceneno,b.fentryid"
    data_detail4 = db.execute(text(sql_4), param).fetchall()

    # atsPoints 明细列表
    sql_3 = " select b.fentryid,b.fsceneno,b.fpoint_no fpointno "
    sql_3 = sql_3 + " ,c.fpoint_name fpointName"
    sql_3 = sql_3 + " from s_tc_scene_head a"
    sql_3 = sql_3 + " inner join s_tc_scene_point b on a.fsceneno=b.fsceneno"
    sql_3 = sql_3 + " left join a_ts_point c on b.fpoint_no=c.fpoint_no"
    sql_3 = sql_3 + " where a.fscenetype=4 "
    if app.fsiteNo is not None:
        sql_3 = sql_3 + " and a.fsite_no= :fsiteNo "
    if app.fpointNo is not None:
        sql_3 = sql_3 + " and b.fpoint_no= :fpointNo "
    sql_3 = sql_3 + " order by b.fsceneno,b.fentryid"
    data_detail = db.execute(text(sql_3), param).fetchall()

    # sTsEquipmentList 明细列表
    sql_2 = " select b.fsceneno,b.fentryid,b.feuipmentid,b.factiontype,b.faction,b.finstructions"
    sql_2 = sql_2 + ",b.fequipmentbarcode,b.fposition,c.fdictname feuipmentname,e.fequipmenttag"
    sql_2 = sql_2 + " from s_tc_scene_head a"
    sql_2 = sql_2 + " inner join s_tc_scene_point s on a.fsceneno=s.fsceneno"
    sql_2 = sql_2 + " inner join s_tc_scene_detail b on a.fsceneno=b.fsceneno"
    sql_2 = sql_2 + " inner join s_ts_equipment_dict c on b.feuipmentid=c.fdictno"
    sql_2 = sql_2 + " left join s_tc_equipment_inv e on e.fequipmentbarcode=b.fequipmentbarcode"
    sql_2 = sql_2 + " where a.fscenetype=4"
    if app.fsiteNo is not None:
        sql_2 = sql_2 + " and a.fsite_no= :fsiteNo "
    if app.fpointNo is not None:
        sql_2 = sql_2 + " and s.fpoint_no= :fpointNo "
    sql_2 = sql_2 + " order by b.factiontype,b.fsceneno,b.fentryid"
    data_detail2 = db.execute(text(sql_2), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsceneno'] = data[key][0]
        d1['fscenename'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['fscenetype'] = data[key][3]
        d1['fteacherno'] = data[key][4]
        d1['fwhere'] = data[key][5]
        d1['fvalue'] = data[key][6]
        d1['fwhere2'] = data[key][7]
        d1['fvalue2'] = data[key][8]
        d1['fexectime'] = data[key][9]
        d1['fexecweek1'] = data[key][10]
        d1['fexecweek2'] = data[key][11]
        d1['fexecweek3'] = data[key][12]
        d1['fexecweek4'] = data[key][13]
        d1['fexecweek5'] = data[key][14]
        d1['fexecweek6'] = data[key][15]
        d1['fexecweek7'] = data[key][16]
        d1['foperator'] = data[key][17]
        d1['fupddate'] = data[key][18]
        d1['frange'] = data[key][19]
        d1['fpointNo'] = data[key][20]
        d1['fpointName'] = None
        d1['executed'] = "执行中"
        d1['isExecuted'] = ""
        sbillno = data[key][0]

        weekList = list()
        if data[key][2] == "1":
            weekList.append("1")
        if data[key][3] == "1":
            weekList.append("2")
        if data[key][4] == "1":
            weekList.append("3")
        if data[key][5] == "1":
            weekList.append("4")
        if data[key][6] == "1":
            weekList.append("5")
        if data[key][7] == "1":
            weekList.append("6")
        if data[key][8] == "1":
            weekList.append("7")
        print(weekList)
        d1['fexecweekList'] = weekList

        sTcSceneItemList = []
        for j4 in range(len(data_detail4)):
            if (str(data_detail4[j4][0]) == str(sbillno)):
                d4 = {}
                d4['fsceneno'] = data_detail4[j4][0]
                d4['fno'] = data_detail4[j4][1]
                d4['fairitem'] = data_detail4[j4][2]
                d4['fariname'] = data_detail4[j4][3]
                d4['ffield'] = data_detail4[j4][4]
                d4['fwhere'] = data_detail4[j4][5]
                d4['fvalue'] = data_detail4[j4][6]
                d4['fwhere2'] = data_detail4[j4][7]
                d4['fvalue2'] = data_detail4[j4][8]
                sTcSceneItemList.append(d4)

        atsPoints = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][1]) == str(sbillno)):
                d2 = {}
                d2['fentryid'] = data_detail[j1][0]
                d2['fsceneno'] = data_detail[j1][1]
                d2['fpointNo'] = data_detail[j1][2]
                d2['fpointName'] = data_detail[j1][3]
                atsPoints.append(d2)

        sTsEquipmentList = []  # 超标设备
        sTsEquipmentList2 = []  # 达标设备
        for j2 in range(len(data_detail2)):
            if (str(data_detail2[j2][0]) == str(sbillno)):
                d3 = {}
                d3['fsceneno'] = data_detail2[j2][0]
                d3['fentryid'] = data_detail2[j2][1]
                d3['feuipmentid'] = data_detail2[j2][2]
                d3['factiontype'] = data_detail2[j2][3]
                d3['faction'] = data_detail2[j2][4]
                d3['finstructions'] = data_detail2[j2][5]
                d3['fequipmentbarcode'] = data_detail2[j2][6]
                d3['fposition'] = data_detail2[j2][7]
                d3['feuipmentname'] = data_detail2[j2][8]
                d3['fequipmenttag'] = data_detail2[j2][9]
                d3['fimage'] = None

                if int(data_detail2[j2][1]) == 1:
                    sTsEquipmentList.append(d3)
                else:
                    sTsEquipmentList2.append(d3)

        d1["atsPoints"] = atsPoints
        d1["sTsEquipmentList"] = sTsEquipmentList
        d1["sTcSceneItemList"] = sTcSceneItemList
        d1["sTsEquipmentList2"] = sTsEquipmentList2
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class SceneEquipmentDetail(BaseModel):
    """场景设备明细"""
    feuipmentid: int = Field(..., description="设备ID")
    faction: str = Field(..., description="操作类型")
    finstructions: str = Field(..., description="指令内容")
    fequipmentbarcode: str = Field(..., description="设备条码")
    factiontype: int = Field(1, description="操作类型，默认为1")


class SceneDataUpdate(BaseModel):
    """场景数据更新模型"""
    fsceneno: int = Field(..., description="场景编号")
    fscenename: str = Field(..., description="场景名称")
    fexectime: str = Field(..., description="执行时间")
    fscenetype: int = Field(..., description="场景类型")
    fexecweek1: int = Field(..., description="周一执行标志")
    fexecweek2: int = Field(..., description="周二执行标志")
    fexecweek3: int = Field(..., description="周三执行标志")
    fexecweek4: int = Field(..., description="周四执行标志")
    fexecweek5: int = Field(..., description="周五执行标志")
    fexecweek6: int = Field(..., description="周六执行标志")
    fexecweek7: int = Field(..., description="周日执行标志")
    foperator: int = Field(..., description="操作人")

    # 可选字段
    fsiteNo: Optional[str] = Field(None, description="站点编号")
    fteacherno: Optional[str] = Field(None, description="教师编号")

    # 关联列表
    sTsEquipmentList: List[SceneEquipmentDetail] = Field(..., description="场景设备列表")


@data_app_scene.post("/update", summary="修改场景数据")
def update_data(scene: SceneDataUpdate, db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from s_tc_scene_detail where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": scene.fsceneno
            })

            jsondata = scene.sTsEquipmentList
            for data in jsondata:
                sql = "insert into s_tc_scene_detail(fsceneno,feuipmentid,faction,finstructions,fequipmentbarcode,factiontype)"
                sql = sql + " values( :fsceneno , :feuipmentid , :faction , :finstructions , :fequipmentbarcode ,1);"
                db.execute(text(sql), {
                    "fsceneno": scene.fsceneno,
                    "feuipmentid": data.feuipmentid,
                    "faction": data.faction,
                    "finstructions": data.finstructions,
                    "fequipmentbarcode": data.fequipmentbarcode,
                })
            sql = "update s_tc_scene_head "
            sql = sql + " set fscenename= :fscenename ,fexectime= :fexectime "
            sql = sql + ",fscenetype= :fscenetype ,fexecweek1= :fexecweek1 ,fexecweek2= :fexecweek2 "
            sql = sql + ",fexecweek3= :fexecweek3 ,fexecweek4= :fexecweek4 ,fexecweek5= :fexecweek5 "
            sql = sql + ",fexecweek6= :fexecweek6 ,fexecweek7= :fexecweek7 "
            sql = sql + ",fupddate=now(),foperator= :foperator "
            sql = sql + " where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fscenename": scene.fscenename,
                "fexectime": scene.fexectime,
                "fscenetype": scene.fscenetype,
                "fexecweek1": scene.fexecweek1,
                "fexecweek2": scene.fexecweek2,
                "fexecweek3": scene.fexecweek3,
                "fexecweek4": scene.fexecweek4,
                "fexecweek5": scene.fexecweek5,
                "fexecweek6": scene.fexecweek6,
                "fexecweek7": scene.fexecweek7,
                "foperator": scene.foperator,
                "fsceneno": scene.fsceneno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
